<?php
require_once './PhpExcel/PHPExcel.php';
require_once './functions.php';

$excel = PHPExcel_IOFactory::load('ffrandonnee-21.xlsx');

$sheet = $excel->getSheet(0);

$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O');
$names = array('A' => 'id', 'B' => 'nom', 'C' => 'adresse', 'D' => 'url_ffr1', 'E' => 'lien1', 'F' => 'url_ffr2', 'G' => 'lien2', 'H' => 'portable', 'I' => 'telephone', 'J' => 'email', 'K' => 'internet', 'L' => 'name', 'M' => 'prenom', 'N' => 'adress', 'O' => 'code_postal');
$rows = 751;

$table = array();

//echo '<table border="1">';

for($i = 1; $i <= $rows; $i++){
    $data = array(
        'id' => 0,
        'nom' => '',
        'adresse' => '',
        'url_ffr1' => null,
        'url_ffr2' => null,
        'telephone' => '',
        'email' => '',
        'internet' => '',
        'code_postal' => '',
        'formulaire' => '',
    );
    //echo '<tr>';
    foreach($cols as $col){
        $colName = $names[$col];
        $cell = $col.$i;
        $cellValue = $sheet->getCell($cell)->getValue();
        //echo '<td>' .$cellValue .'</td>';
        switch ($colName){
            case 'adresse':
                $adress = extractAdress($cellValue);
                $data['code_postal'] = $adress['cp'];
                $data['adresse'] = addslashes($adress['adresse']);
                break;
            case 'url_ffr1':
                $data['url_ffr1'] = addslashes($cellValue);
                $data['formulaire'] = isUrl($cellValue) ? $cellValue : $data['formulaire'];
                break;
            case 'url_ffr2':
                $data['url_ffr2'] = addslashes($cellValue);
                $data['formulaire'] = isUrl($cellValue) ? $cellValue : $data['formulaire'];
                break;
            case 'nom':
                $data['nom'] = addslashes($cellValue);
                break;
            case 'id':
                $data['id'] = $cellValue;
                break;
        }
    }
    $table[] = $data;
    //echo '</tr>';
}
//echo '</table>';

$queries = array();

foreach($table as $t){
    $sql = "INSERT INTO extractor (id, nom, url_ffr1, url_ffr2, adresse, code_postal, url_formulaire) VALUES({$t['id']}, '{$t['nom']}', '{$t['url_ffr1']}', '{$t['url_ffr2']}', '{$t['adresse']}', '{$t['code_postal']}', '{$t['formulaire']}');";
    $queries[] = $sql;
    echo $sql.'<br>';
}
